import xlrd
import xlwt
import openpyxl
from xlutils.copy import copy    
import random


for nnnbbb in range(1,4):
    path3 = 'C:\\Users\\xiong\\Desktop\\22\\'+str(nnnbbb) + '.xls'  

    workbook_new = xlwt.Workbook()  # 新建一个工作簿
    sheet2 = workbook_new.add_sheet('sheet1')  
    
    dics = []
    names = []
    n = 0
    path2 = 'C:\\Users\\xiong\\Desktop\\新建文件夹\\' +str(nnnbbb)+'.xls'  
    
    workbook = xlrd.open_workbook(path2) 
    
    sheet = workbook.sheet_by_index(0)
    
    list11 = []
    iInlist = []
    for i in range (0,sheet.ncols):
        list11.append(sheet.cell(0,i).value)
    for j in list11:
        if '区系' in j:
            iInlist.append(list11.index(j)-6)
            list11[list11.index(j)] = ''
    
    for i in range(1,sheet.nrows):
        for j in iInlist:
            dic = {}
            if len(sheet.cell(i,j).value.strip()) > 0:
                if len(sheet.cell(i,j+2).value) == 0 or len(sheet.cell(i,j+4).value) == 0 or len(sheet.cell(i,j+6).value) == 0:
                    if len(sheet.cell(i,j+2).value) == 0:
                        print(sheet.cell(i,j).value+'缺了中文分类')
                    elif len(sheet.cell(i,j+4).value) == 0:
                        print(sheet.cell(i,j).value+'缺了生活型')
                    elif len(sheet.cell(i,j+6).value) == 0:
                        print(sheet.cell(i,j).value+'缺了区系')
                        
                    if sheet.cell(i,j).value.strip() not in names:
                        dic['name'] = sheet.cell(i,j).value.strip()
                        dic['Latinname'] = sheet.cell(i,j+1).value.strip()
                        dic['type'] = sheet.cell(i,j+2).value.strip()
                        dic['Latintype'] = sheet.cell(i,j+3).value.strip()
                        dic['living'] = sheet.cell(i,j+4).value.strip()
                        dic['ecology'] = sheet.cell(i,j+5).value.strip()
                        dic['area'] = sheet.cell(i,j+6).value.strip()  
                                        
                        zhong = ''.join(ii for ii in sheet.cell(0,j).value if ord(ii)>256)
                        lei = sheet.cell(i,0).value
                        G = i
                        while len(sheet.cell(G,2).value) == 0:
                            G-=1
                        qunxi  = sheet.cell(G,2).value
                        
                        dic['beizhu'] ='（' + zhong+ str(lei)+';'+str(qunxi)+'）'
                        names.append(dic['name'] )
                        dics.append(dic)
                    else :
                        zhong = ''.join(ii for ii in sheet.cell(0,j).value if ord(ii)>256)
                        lei = sheet.cell(i,0).value
                        G = i
                        while len(sheet.cell(G,2).value) == 0:
                            G-=1
                        qunxi  = sheet.cell(G,2).value
                        
                        dic = dics[names.index(sheet.cell(i,j).value.strip())]
                        dic['beizhu'] = '（' + zhong+ str(lei)+';'+str(qunxi)+'）'+dic['beizhu']
                    
    for dicc in dics:
        n+=1
        sheet2.write(n,0,label=dicc['name'])
        sheet2.write(n,1,label=dicc['Latinname'])
        sheet2.write(n,2,label=dicc['type'])
        sheet2.write(n,3,label=dicc['Latintype'])
        sheet2.write(n,4,label=dicc['living'])
        sheet2.write(n,5,label=dicc['ecology'])
        sheet2.write(n,6,label=dicc['area'])
        sheet2.write(n,7,label=dicc['beizhu'])
                    
            
    workbook_new.save(path3)